﻿using System;
using System.Collections.Generic;
using System.ComponentModel.Composition;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm;
using com.nm.library;
using com.nm.library.Excels;
using com.nm.library.Helper;
using CommodityTransferAPI.Model;
using NHibernateVertica;
using Nancy.Extensions;
using Vertica.Data.VerticaClient;

//城市间物流天数设置
namespace CommodityTransferAPI.Import
{
    [Export(typeof(ExcelImport))]
    public class CityLogisticsSettingsImport : ExcelImport
    {
        private static int num = 0;
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();
        public override ExcelImportType Type => ExcelImportType.CITYLOGISTICSSETTINGS;

        public override Dictionary<string, ImportVerify> DictFields => dictFields;

        /// <summary>
        /// Excel字段映射
        /// </summary>
        private static readonly Dictionary<string, ImportVerify> dictFields = new List<ImportVerify> {
            new ImportVerify{ ColumnName="公司代码",    FieldName="CompanyID" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="发货城市",    FieldName="ShipCity" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="到达城市",    FieldName="ReceivingCity" ,DataType=typeof(string),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
            new ImportVerify{ ColumnName="物流天数",    FieldName="TransDays" ,DataType=typeof(int),VerifyFunc=(e,extra)=> ExcelImportHelper.GetCellMsg(e.CellValue,e.ColName,200,true,false)},
        }.ToDictionary(e => e.ColumnName, e => e);

        public override object SaveImportData(DataTable dt, Dictionary<string, object> extraInfo, int userId)
        {
            List<CityLogisticsSettings> errorList = new List<CityLogisticsSettings>();
            List<CityLogisticsSettingsDb> importList = new List<CityLogisticsSettingsDb>();
            var result = new ImportResult
            {
                IsSuccess = true,
                ExtraInfo = "导入成功",
            };
            try
            {
                var extrSource = UserInfo.UserExtrSource;
                var compayId = UserInfo.UserCompanyId;
                //缓存导入相关的城市数据
                var list = ModelConvertHelper<CityLogisticsSettings>.ConvertToModel(dt).ToList();
                //如果导入的excel有多条同一城市间的数据，取最后一条
                list.Reverse();
                var cache = CityCache(list);
                var cityLogisticsCache = CityLogisticsCache(list);
                foreach (var item in list)
                {
                    var areaIDBg = cache.Find(s => s.ShipCity == item.ShipCity)?.AreaIDBg;
                    var areaIDEnd = cache.Find(s => s.ShipCity == item.ReceivingCity)?.AreaIDBg;
                    if (!string.IsNullOrWhiteSpace(areaIDBg) && !string.IsNullOrWhiteSpace(areaIDEnd))
                    {
                        item.AreaIDBg = areaIDBg;
                        item.AreaIDEnd = areaIDEnd;
                        item.ExtrSource = extrSource;
                        item.CompanyID = compayId;
                        //判断物流天数不能小于0
                        if (item.TransDays >= 0)
                        {
                            //判断在待添加到DB的列表中是否存在，如果存在就pass
                            if (importList.Exists(s => s.AreaIDBg == item.AreaIDBg &&
                                                       s.AreaIDEnd == item.AreaIDEnd))
                            {
                                //写入错误列表
                                Insert2Error(errorList, item);
                                continue;
                            }

                            //判断是否有同城的数据，如果有同城的数据，就直接导入
                            //判断是否存在导入的发货城市对应db中的收货城市，导入的收货城市也对应db中的发货城市
                            //如果存在，pass
                            if (item.AreaIDBg != item.AreaIDEnd && (item.AreaIDBg != item.AreaIDEnd && cityLogisticsCache.Exists(s => s.AreaIDEnd == item.AreaIDBg && s.AreaIDBg == item.AreaIDEnd)))
                            {
                                //写入错误列表
                                Insert2Error(errorList, item);
                                continue;
                            }
                            CityLogisticsSettingsDb temp = new CityLogisticsSettingsDb()
                            {
                                ExtrSource = item.ExtrSource,
                                CompanyID = item.CompanyID,
                                AreaIDBg = item.AreaIDBg,
                                AreaIDEnd = item.AreaIDEnd,
                                TransDays = item.TransDays
                            };
                            importList.Add(temp);
                        }
                        else
                        {
                            //写入错误列表
                            Insert2Error(errorList, item);
                            continue;
                        }
                    }
                    else
                    {
                        //写入错误列表
                        Insert2Error(errorList, item);
                        continue;
                    }
                }
                /*
                //检验Excel中是否同时存在类似，广州-->深圳和深圳-->广州这样的数据。
                //如果有，则只保留最后一条数据
                if (importList.Count > 0)
                {

                    var tempList = importList.ToList();
                    foreach (var item in tempList)
                    {
                        if (importList.Any(s => s.AreaIDBg == item.AreaIDEnd && s.AreaIDEnd == item.AreaIDBg))
                        {
                            importList.Remove(item);
                            //写入错误列表
                            var shipCity = cache.Find(s => s.AreaIDBg == item.AreaIDBg)?.ShipCity;
                            var receivingCity = cache.Find(s => s.AreaIDBg == item.AreaIDEnd)?.ShipCity;
                            CityLogisticsSettings model = new CityLogisticsSettings()
                            {
                                AreaIDBg = item.AreaIDBg,
                                AreaIDEnd = item.AreaIDEnd,
                                CompanyID = item.CompanyID,
                                ExtrSource = item.ExtrSource,
                                TransDays = item.TransDays,
                                ShipCity = shipCity,
                                ReceivingCity = receivingCity
                            };
                            Insert2Error(errorList, model);
                            continue;
                        }
                    }
                }
                */

                HashSet<string> deleteSqlSet = new HashSet<string>();
                StringBuilder sb = new StringBuilder();

                foreach (var item in importList)
                {
                    deleteSqlSet.Add($"Delete FROM ngoods.ngoods_Mv_Bas_CityTransDay where ExtrSource='{item.ExtrSource}' and  CompanyID='{item.CompanyID}' and AreaIDBg='{item.AreaIDBg}' and AreaIDEnd='{item.AreaIDEnd}'");
                }
                List<CommandInfo> deleteCmdList = new List<CommandInfo>();
                foreach (var item in deleteSqlSet)
                {
                    CommandInfo cmd = new CommandInfo() { CommandText = item };
                    deleteCmdList.Add(cmd);
                }
                //插入DB
                if (importList.Count > 0)
                {

                    DataTable dtImport = DataTableHelper.LinqToDataTable(importList);
                    Helper.ExecuteSqlTran(deleteCmdList, dtImport, "ngoods.ngoods_Mv_Bas_CityTransDay", 1000);
                    result.Message =
                                           $"成功导入{importList.Count}条数据{(errorList.Count == 0 ? "" : $@",有{errorList.Count}条错误数据")}";
                }
                //检查是否有错误，如果有，返回下载错误数据的Excel
                if (errorList.Count > 0)
                {
                    result.Message =
                        $"成功导入{importList.Count}条数据{(errorList.Count == 0 ? "" : $@",有{errorList.Count}条错误数据")}";
                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    var errorDT = DataTableHelper.LinqToDataTable(errorList);
                    result.Url = ExcelUtil.ExportErrorExeclToDB(errorDT, path, DictFields, "城市物流天数错误数据", errorList.Count);
                }
            }
            catch (Exception e)
            {
                result.Message = e.Message;
                result.IsSuccess = false;
                //检查是否有错误，如果有，返回下载错误数据的Excel
                if (errorList.Count > 0)
                {
                    result.Message =
                        $"成功导入{importList.Count}条数据{(errorList.Count == 0 ? "" : $@",有{errorList.Count}条错误数据")}";

                    var path = ExcelImporMapper.GetTemplatePath(Type);
                    var errorDT = DataTableHelper.LinqToDataTable(errorList);
                    result.Url = ExcelUtil.ExportErrorExeclToDB(errorDT, path, DictFields, "城市物流天数错误数据", errorList.Count);
                }
            }
            return result;
        }


        public override DataTable GetExportData(string Keys)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 缓存上传的城市列表
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public List<CityLogisticsSettings> CityCache(List<CityLogisticsSettings> list)
        {
            HashSet<string> setCity = new HashSet<string>();
            foreach (var item in list)
            {
                setCity.Add(item.ShipCity);
                setCity.Add(item.ReceivingCity);
            }
            StringBuilder sbWhere = new StringBuilder();
            foreach (var city in setCity)
            {
                sbWhere.Append(",'");
                sbWhere.Append(city);
                sbWhere.Append("'");
            }
            if (sbWhere.Capacity > 0)
            {
                sbWhere.Remove(0, 1);
            }
            string strSql = $"SELECT AreaID as AreaIDBg,AreaName as ShipCity from nods.nods_erp_Bas_Area WHERE ExtrSource='{UserInfo.UserExtrSource}' and AreaName in ({sbWhere})";
            var dtCache = Helper.ExecuteDataTable(new VerticaCommand(strSql, null));
            var listCache = ModelConvertHelper<CityLogisticsSettings>.ConvertToModel(dtCache);
            return listCache.ToList();
        }

        /// <summary>
        /// 缓存上传的城市中已经存在的物流天数列表
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public List<CityLogisticsSettings> CityLogisticsCache(List<CityLogisticsSettings> list)
        {
            StringBuilder sbShipCity = GetShipCityString(list);
            StringBuilder sbReceivingCity = GetReceivingCityString(list);
            string strSql = $@"
  select
	ct.CompanyID,
	ct.AreaIDBg,
	ct.AreaIDEnd,
	ct.TransDays,
	area.areaname as ShipCity,
	area2.areaname as ReceivingCity
FROM
	ngoods.ngoods_Mv_Bas_CityTransDay ct left join nods.nods_erp_Bas_Area area on
	ct.AreaIDBg = area.areaid  and ct.extrsource = area.extrsource  left join nods.nods_erp_Bas_Area area2 on
	ct.AreaIDEnd = area2.areaid	 and ct.extrsource = area2.extrsource 
	WHERE	
ct.extrsource='{UserInfo.UserExtrSource}' and ct.companyid = '{UserInfo.UserCompanyId}' and 
	area.areaname in({sbReceivingCity}) and area2.areaname in({sbShipCity})
";
            var dtCache = Helper.ExecuteDataTable(new VerticaCommand(strSql, null));
            var listCache = ModelConvertHelper<CityLogisticsSettings>.ConvertToModel(dtCache);
            return listCache.ToList();
        }

        private StringBuilder GetShipCityString(List<CityLogisticsSettings> list)
        {
            HashSet<string> setCity = new HashSet<string>();
            foreach (var item in list)
            {
                setCity.Add(item.ShipCity);
            }
            StringBuilder sbWhere = new StringBuilder();
            foreach (var city in setCity)
            {
                sbWhere.Append(",'");
                sbWhere.Append(city);
                sbWhere.Append("'");
            }
            if (sbWhere.Capacity > 0)
            {
                sbWhere.Remove(0, 1);
            }
            return sbWhere;
        }

        private StringBuilder GetReceivingCityString(List<CityLogisticsSettings> list)
        {
            HashSet<string> setCity = new HashSet<string>();
            foreach (var item in list)
            {
                setCity.Add(item.ReceivingCity);
            }
            StringBuilder sbWhere = new StringBuilder();
            foreach (var city in setCity)
            {
                sbWhere.Append(",'");
                sbWhere.Append(city);
                sbWhere.Append("'");
            }
            if (sbWhere.Capacity > 0)
            {
                sbWhere.Remove(0, 1);
            }
            return sbWhere;
        }

        public void Insert2Error(List<CityLogisticsSettings> errorList, CityLogisticsSettings model)
        {
            errorList.Add(model);
        }
    }

}
